package com.walker.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

@Repository
public class PrestoUtil {

	private static final Log log = LogFactory.getLog(PrestoUtil.class);
	
	@Value("${presto.jdbc.url}")
	private String jdbcUrl;
	
	@Value("${presto.jdbc.password}")
	private String jdbcPassowrd;
	
	public Connection getConnection(String database,String username) throws Exception {
		String jdbcurl = jdbcUrl + "/" +database;
		Connection conn = null;
		try {
			Class.forName("io.prestosql.jdbc.PrestoDriver");
			conn = DriverManager.getConnection(jdbcurl, username, jdbcPassowrd);
		} catch (Exception e) {
			if(log.isErrorEnabled()) {
				log.error(e.getMessage(),e);
			}
			throw new Exception(e.getMessage());
		} 
		return conn;
	}
	
	public void executeSql(Connection conn,String sql) throws Exception{
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			stmt.execute(sql);
		} catch (SQLException e) {
			if(log.isErrorEnabled()) {
				log.error(e.getMessage(),e);
			}
			throw new Exception(e.getMessage());
		}finally {
			close(stmt);
		}
	}
	
	public void executeSql(String database,String username,String sql) throws Exception{
		Statement stmt = null;
		Connection conn = null;
		try {
			conn = getConnection(database,username);
			stmt = conn.createStatement();
			stmt.execute(sql);
		} catch (SQLException e) {
			if(log.isErrorEnabled()) {
				log.error(e.getMessage(),e);
			}
			throw new Exception(e.getMessage());
		}finally {
			close(stmt,conn);
		}
	}
	
	public List<Map<String,Object>> queryForList(String database,String username,String sql) throws Exception{
		Statement stmt = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = getConnection(database,username);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			return resultSetToList(rs);
		} catch (SQLException e) {
			if(log.isErrorEnabled()) {
				log.error(e.getMessage(),e);
			}
			throw new Exception(e.getMessage());
		}finally {
			close(rs,stmt,conn);
		}
	}
	
	/**
	 **获取列字段名称
	 * @param table
	 * @return
	 * @throws Exception 
	 */
	public List<Map<String,Object>> getColumnInfo(String database,String username,String table) throws Exception{
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = getConnection(database,username);
			DatabaseMetaData dbMetaData = conn.getMetaData();
			rs = dbMetaData.getColumns(null, database, table , "%");
			while(rs.next()){
				Map<String,Object> map = new LinkedHashMap<String,Object>();
				map.put("col_name",rs.getString("COLUMN_NAME"));
				map.put("data_type",rs.getString("TYPE_NAME").toLowerCase());
				map.put("comment",rs.getString("REMARKS"));
				list.add(map);
			}
		}catch(Exception e) {
			if(log.isErrorEnabled()) {
				log.error(e.getMessage(),e);
			}
			throw new Exception(e.getMessage());
		}finally {
			close(rs);
			close(conn);
		}
		return list;
	}
	
	public List<Map<String, Object>> resultSetToList(ResultSet rs) throws SQLException{
		if (rs == null) {
			return new ArrayList<Map<String, Object>>();
		}
		ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
		int columnCount = md.getColumnCount(); // 返回此 ResultSet对象中的列数
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Map<String, Object> rowData = new HashMap<String, Object>();
		while (rs.next()) {
			rowData = new HashMap<String, Object>(columnCount);
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));
			}
			list.add(rowData);
		}
		return list;
	}
	
	/**
	 * 释放数据库连接
	 * 
	 * @param conn
	 */
	public void close(Connection conn) {
		if (conn == null) {
			return;
		}
		try {
			if (conn.isClosed()) {
				return;
			}
			conn.close();
		} catch (SQLException e) {
			if(log.isErrorEnabled()){
				log.error(e.getMessage(), e);
			}
		}
	}

	public void close(Statement stmt) {
		if(stmt == null){
			return;
		}
		try {
			stmt.close();
		} catch (SQLException e) {
			if(log.isErrorEnabled()){
				log.error(e.getMessage(), e);
			}
		}
	}

	public void close(ResultSet rs) {
		if(rs == null){
			return;
		}
		try {
			rs.close();
		} catch (SQLException e) {
			if(log.isErrorEnabled()){
				log.error(e.getMessage(), e);
			}
		}
	}

	public void close(Statement stmt, Connection conn) {
		close(stmt);
		close(conn);
	}

	public void close(ResultSet rs, Statement stmt, Connection conn) {
		close(rs);
		close(stmt);
		close(conn);
	}
}